import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class test {

	/**
	 * @param args
	 */

	private static Connection connect = null;
	private static Statement statement = null;
	private static PreparedStatement preparedStatement = null;
	private static ResultSet resultSet = null;

	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub

		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager
					.getConnection("jdbc:mysql://localhost/backup?"
							+ "user=backup&password=networker");

			// Statements allow to issue SQL queries to the database
			statement = connect.createStatement();
			// Result set get the result of the SQL query
			resultSet = statement
					.executeQuery("select * from backup.avamar_gc");
			writeResultSet(resultSet);

			// PreparedStatements can use variables and are more efficient
			preparedStatement = connect
					.prepareStatement("insert into backup.avamar_gc (values (default,?,?,?,?)");
			// "myuser, webpage, datum, summary, COMMENTS from FEEDBACK.COMMENTS");
			// Parameters start with 1
			
			//preparedStatement.setDate(1, new java.sql.Date(2013, 06, 01));
			preparedStatement.setString(1,"1");
			preparedStatement.setString(2, "123");
			preparedStatement.setString(3, "3454");
			preparedStatement.setString(4, "234323");
			//preparedStatement.setString(5, "11");
			preparedStatement.executeUpdate();


			preparedStatement = connect
					.prepareStatement("SELECT new_data, bu, removed_data, gc  from backup.avamar_gc");
			resultSet = preparedStatement.executeQuery();
			writeResultSet(resultSet);

			// Remove again the insert comment
			preparedStatement = connect
					.prepareStatement("delete from backup.avamar_gc where gc= ? ; ");
			preparedStatement.setString(1, "345");
			preparedStatement.executeUpdate();

			resultSet = statement
					.executeQuery("select * from backup.avamar_gc");
			//writeMetaData(resultSet);

		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}

	}

	private static void writeMetaData(ResultSet resultSet) throws SQLException {
		//   Now get some metadata from the database
		// Result set get the result of the SQL query

		System.out.println("The columns in the table are: ");

		System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
		for  (int i = 1; i<= resultSet.getMetaData().getColumnCount(); i++){
			System.out.println("Column " +i  + " "+ resultSet.getMetaData().getColumnName(i));
		}
	}

	private static void writeResultSet(ResultSet resultSet) throws SQLException {
		// ResultSet is initially before the first data set
		while (resultSet.next()) {
			// It is possible to get the columns via name
			// also possible to get the columns via the column number
			// which starts at 1
			// e.g. resultSet.getSTring(2);
			String new_data = resultSet.getString("new_data");
			String website = resultSet.getString("gc");
			String summary = resultSet.getString("removed_data");
			//String date = resultSet.getString("bu");
			String date = resultSet.getString(1);
			//String comment = resultSet.getString("net_change");
			System.out.println("new_data: " + new_data);
			System.out.println("Website: " + website);
			System.out.println("Summary: " + summary);
			System.out.println("Date: " + date);
			//System.out.println("Comment: " + comment);
		}
	}

	// You need to close the resultSet
	private static void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}



}


